package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.domain.NewsCategory;
import cn.com.checne.model.domain.NewsInfo;
import cn.com.checne.model.dto.NewsQuery;
import cn.com.checne.util.RegexUtils;
import cn.com.checne.util.StringTools;
import cn.com.checne.util.TimeUtils;
import cn.com.checne.util.XMLUtils;


@Repository
public class NewsInfoDao {
	protected static final Logger log = Logger.getLogger(NewsInfoDao.class);

	/**
	 * create
	 * @param news
	 * @return boolean
	 */
	public boolean create(NewsInfo news) {
		boolean result ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_news(lg_id_fk,news_cate_id_fk,news_title,news_summary,news_publishdate,news_publishfrom,news_author,news_contenttype,news_content," +
					"news_meta_keyword,news_meta_content,news_isshowindex,news_isshowlist,news_istop,news_firstimage)" +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0 ;
			pstmt.setInt(++i,news.getLanguageId());
			pstmt.setInt(++i,news.getCateId());
			pstmt.setString(++i,news.getTitle());
			pstmt.setString(++i,news.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(news.getPublishDate().getTime()));
			pstmt.setString(++i,news.getPublishFrom());
			pstmt.setString(++i,news.getAuthor());
			pstmt.setInt(++i,news.getContentType());
			pstmt.setString(++i,news.getContent());
			
			pstmt.setString(++i,news.getMetaKeyword());
			pstmt.setString(++i,news.getMetaContent());
			
			pstmt.setInt(++i,news.getIsShowIndex());
			pstmt.setInt(++i,news.getIsShowList());
			pstmt.setInt(++i,news.getIsTop());
			
			pstmt.setString(++i,news.getFirstImage());

			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

    /**
     *
     * @param news
     * @return
     */
	public boolean update(NewsInfo news) {
		boolean result ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_news set " +
					"lg_id_fk = ?,news_cate_id_fk = ?,news_title = ?,news_summary = ?,news_publishdate =? ,news_publishfrom = ?,news_author =?,news_contenttype = ?,news_content=?," +
					"news_meta_keyword =?,news_meta_content=?,news_isshowindex=?,news_isshowlist=?,news_istop=?,news_firstimage=? where news_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,news.getLanguageId());
			pstmt.setInt(++i,news.getCateId());
			pstmt.setString(++i,news.getTitle());
			pstmt.setString(++i,news.getSummary());
			pstmt.setTimestamp(++i, new java.sql.Timestamp(news.getPublishDate().getTime()));
			pstmt.setString(++i,news.getPublishFrom());
			pstmt.setString(++i,news.getAuthor());
			pstmt.setInt(++i,news.getContentType());
			pstmt.setString(++i,news.getContent());
			
			pstmt.setString(++i,news.getMetaKeyword());
			pstmt.setString(++i,news.getMetaContent());
			
			pstmt.setInt(++i,news.getIsShowIndex());
			pstmt.setInt(++i,news.getIsShowList());
			pstmt.setInt(++i,news.getIsTop());
			
			pstmt.setString(++i,news.getFirstImage());
			
			pstmt.setInt(++i, news.getNewsId());
			
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_news where news_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	/**
	 * 
	 * @param newsId
	 * @return
	 */
	public NewsInfo getNewsById(int newsId) {
		NewsInfo news = new NewsInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_news where news_id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, newsId);
			ResultSet rs = pstmt.executeQuery();
			List<NewsInfo> list = getResult(rs);
			news = (list != null && list.size() > 0 ) ? list.get(0) : news ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return news;
	}
	/**
	 * 
	 * @param query
	 * @param start
	 * @param num
	 * @return
	 */
	public List<NewsInfo> getNewsListByCondition(NewsQuery query,int start,int num) {
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_news where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and news_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and news_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and news_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and news_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (news_title like ? or news_meta_keyword like ? or news_meta_content like ? or news_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and news_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and news_publishdate <= ? ";
			}
			sql += " order by news_publishdate desc  limit ?,? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public List<NewsInfo> getNewsImagesListByCondition(NewsQuery query,int start,int num) {
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_news where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and news_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and news_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and news_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and news_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (news_title like ? or news_meta_keyword like ? or news_meta_content like ? or news_content like ? )";
			}
			sql += " and news_firstimage <> ''  order by news_publishdate desc  limit ?,? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

    /**
     *
     * @param query
     * @return
     */
	public int getNewsCountByCondition(NewsQuery query) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_news where 1=1 " ;
			if(query.getLanguageId() > 0){
				sql += " and lg_id_fk = ? " ;
			}
			if(query.getCateId() > 0){
				sql += " and news_cate_id_fk = ? " ;
			}
			if(query.getIsShowIndex() > -1){
				sql += " and news_isshowindex = ? " ;
			}
			if(query.getIsShowList() > -1){
				sql += " and news_isshowlist = ? " ;
			}
			if(query.getIsTop() > -1){
				sql += " and news_istop = ? " ;
			}
			if(StringUtils.isNotBlank(query.getKw())){
				sql += " and (news_title like ? or news_meta_keyword like ? or news_meta_content like ? or news_content like ? )";
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				sql += " and news_publishdate >= ? ";
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				sql += " and news_publishdate <= ? ";
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0 ;
			if(query.getLanguageId() > 0){
				pstmt.setInt(++i, query.getLanguageId());
			}
			if(query.getCateId() > 0){
				pstmt.setInt(++i, query.getCateId());
			}
			if(query.getIsShowIndex() > -1){
				pstmt.setInt(++i, query.getIsShowIndex());
			}
			if(query.getIsShowList() > -1){
				pstmt.setInt(++i, query.getIsShowList());
			}
			if(query.getIsTop() > -1){
				pstmt.setInt(++i, query.getIsTop());
			}
			if(StringUtils.isNotBlank(query.getKw())){
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
				pstmt.setString(++i, "%" + query.getKw() + "%");
			}
			if(StringUtils.isNotBlank(query.getStartTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getStartTime()).getTime()));
			}
			if(StringUtils.isNotBlank(query.getEndTime())){
				pstmt.setTimestamp(++i, new java.sql.Timestamp(TimeUtils.formatDate(query.getEndTime()).getTime()));
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	
	public boolean setShowIndex(int flag,int...newsIds){
		boolean result ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_news set news_isshowindex = ? where news_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int newsId : newsIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,newsId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	public boolean setShowList(int flag,int...newsIds){
		boolean result ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_news set news_isshowlist = ? where news_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			for(int newsId : newsIds){
				int i = 0 ;
				pstmt.setInt(++i,flag);
				pstmt.setInt(++i,newsId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	
	
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<NewsInfo> getResult(ResultSet rs) {
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		try {
			NewsInfo news = null ;
			while(rs.next()) {
				news = new NewsInfo();
				news.setNewsId(rs.getInt("news_id"));
				news.setTitle(rs.getString("news_title"));
				news.setSummary(rs.getString("news_summary"));
				news.setPublishDate(rs.getTimestamp("news_publishdate"));
				news.setPublishFrom(rs.getString("news_publishfrom"));
				news.setAuthor(rs.getString("news_author"));
				news.setContentType(rs.getInt("news_contenttype"));
				news.setContent(rs.getString("news_content"));
				news.setMetaKeyword(rs.getString("news_meta_keyword"));
				news.setMetaContent(rs.getString("news_meta_content"));
				news.setIsShowIndex(rs.getInt("news_isshowindex"));
				news.setIsShowList(rs.getInt("news_isshowlist"));
				news.setIsTop(rs.getInt("news_istop"));
				news.setFirstImage(rs.getString("news_firstimage"));
				
				news.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(news.getLanguageId());
				news.setLanguageInfo(lg);
				
				news.setCateId(rs.getInt("news_cate_id_fk"));
				NewsCategory cate = new NewsCategoryDao().getNewsCategory(news.getCateId());
				news.setNewsCategory(cate);
				
				list.add(news);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public void checCNNewsSynch(String yestoday) {
		List<NewsInfo> checCNNewsList = new ChecNewsDao()
				.getYestodayNewsFromCNList(yestoday);
		for (NewsInfo news : checCNNewsList) {
			NewsInfo n = new NewsInfo();
			n.setCateId(2);
			n.setTitle(news.getTitle());
			if (StringUtils.isBlank(news.getSummary()))
				n.setSummary(StringTools.substring(
						StringTools.removeHtml(news.getContent()), 100));
			else {
				n.setSummary(StringTools.removeHtml(news.getSummary()));
			}
			String content = news.getContent();
			n.setPublishDate(news.getPublishDate());
			n.setIsShowIndex(1);
			n.setIsShowList(1);
			n.setLanguageId(1);
			n.setAuthor("管理员");
			n.setPublishFrom("华电工程");

			String firstImage = "";
			String srcRegex = "src\\s*=\\s*[\"|'](.+?[jpg|gif|png|bmp|ico])[\"|']";
			List<String> allImages = RegexUtils.getStringGoup1(content, srcRegex);

			String contentResult = content;
			/*for (String image : allImages) {
				String relativePath = "userfiles/image/";
				String projectBasePath = PropertiesUtil.getProjectBasePath();
				FileUtils.mkdirs(projectBasePath + relativePath);
				String extend = FilenameUtils.getExtension(image);
				firstImage = relativePath
						+ DateFormatUtils.format(new Date(),
								"yyyyMMddHHmmssSSS") + "." + extend;
				String imgUrl = "http://" + PropertiesUtil.getChecIp() + image;
				try {
					URL url = new URL(imgUrl);
					HttpURLConnection conn = (HttpURLConnection) url
							.openConnection();
					InputStream is = conn.getInputStream();
					int len = 0;
					byte[] buffer = new byte[1024];
					FileOutputStream fos = new FileOutputStream(projectBasePath
							+ firstImage);
					while ((len = is.read(buffer)) > 0) {
						fos.write(buffer, 0, len);
					}
					fos.close();
					contentResult = contentResult.replaceAll(image, firstImage);
				} catch (MalformedURLException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}*/
			//外站的图片不再进行下载处理。http://www.chec.com.cn
			for(String image : allImages){
				if(StringUtils.isNotBlank(image) && ! image.toLowerCase().startsWith("http")){
					contentResult = contentResult.replace(image, "http://www.chec.com.cn" + image);
					firstImage = "http://www.chec.com.cn" + image ;
				}
			}
			/**
			 * 强制在正文内容设置字体样式。
			 * chec.com.cn中文的网站抓取到的内容为<p>&nbsp;&nbsp;&nbsp;</p>
			 * 需要把第一次出现<p>&nbsp;&nbsp;&nbsp;</p>的地方替换成<p>&nbsp;&nbsp;&nbsp;
			 */
			contentResult = contentResult.replaceFirst("<p>&nbsp;&nbsp;&nbsp;", "<p>");
			/*while(contentResult.startsWith("&nbsp;")){
				contentResult = StringUtils.removeStart(contentResult, "&nbsp;");
			}*/
			n.setContent("<span style=\"font-size: medium\">" + contentResult + "</span>" );
			n.setFirstImage(firstImage);
			if (create(n)) {
				NewsQuery query = new NewsQuery();
				query.setLanguageId(1);
				query.setCateId(1);
				XMLUtils.buildXML(getNewsImagesListByCondition(query, 0, 6), "cn");
			}
		}
	}

	public void checENNewsSynch(String yestoday) {
		List<NewsInfo> checCNNewsList = new ChecNewsDao()
				.getYestodayNewsFromENList(yestoday);
		for (NewsInfo news : checCNNewsList) {
			NewsInfo n = new NewsInfo();
			n.setCateId(6);
			n.setTitle(news.getTitle());
			if (StringUtils.isBlank(news.getSummary()))
				n.setSummary(StringTools.substring(
						StringTools.removeHtml(news.getContent()), 100));
			else {
				n.setSummary(StringTools.removeHtml(news.getSummary()));
			}
			String content = news.getContent();
			n.setPublishDate(news.getPublishDate());
			n.setIsShowIndex(1);
			n.setIsShowList(1);
			n.setLanguageId(2);
			n.setAuthor("admin");
			n.setPublishFrom("chec");

			String firstImage = "";
			String srcRegex = "src\\s*=\\s*[\"|'](.+?[jpg|gif|png|bmp|ico])[\"|']";
			List<String> allImages = RegexUtils.getStringGoup1(content,srcRegex);

			/*List<String> reverseOrder = new ArrayList<String>();
			for (int i = allImages.size() - 1; i > -1; i--) {
				reverseOrder.add(((String) allImages.get(i)).replace(
						"http://en.chec.com.cn", ""));
			}*/
			String contentResult = content;
			/*for (String image : reverseOrder) {
				String relativePath = "userfiles/image/";
				String projectBasePath = PropertiesUtil.getProjectBasePath();
				FileUtils.mkdirs(projectBasePath + relativePath);
				String extend = FilenameUtils.getExtension(image);
				firstImage = relativePath
						+ DateFormatUtils.format(new Date(),
								"yyyyMMddHHmmssSSS") + "." + extend;
				String imgUrl = "http://" + PropertiesUtil.getChecIp() + image;
				try {
					URL url = new URL(imgUrl);
					HttpURLConnection conn = (HttpURLConnection) url
							.openConnection();
					InputStream is = conn.getInputStream();
					int len = 0;
					byte[] buffer = new byte[1024];
					FileOutputStream fos = new FileOutputStream(projectBasePath
							+ firstImage);
					while ((len = is.read(buffer)) > 0) {
						fos.write(buffer, 0, len);
					}
					fos.close();
					contentResult = contentResult.replaceAll(image,firstImage);
				} catch (MalformedURLException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}*/
			//外站的图片不再进行下载处理。http://en.chec.com.cn
			for(String image : allImages){
				if(StringUtils.isNotBlank(image) && ! image.toLowerCase().startsWith("http")){
					contentResult = contentResult.replace(image, "http://en.chec.com.cn" + image);
					firstImage = "http://en.chec.com.cn" + image ;
				}
			}
			while(contentResult.startsWith("&nbsp;")){
				contentResult = StringUtils.removeStart(contentResult, "&nbsp;");
			}
			n.setContent("<span style=\"font-size: medium\">" + contentResult + "</span>" );
			n.setFirstImage(firstImage);
			if (create(n)) {
				NewsQuery query = new NewsQuery();
				query.setLanguageId(2);
				query.setCateId(6);
				XMLUtils.buildXML(getNewsImagesListByCondition(query, 0, 6), "en");
			}
		}
	}
}